package mh.dal;

//Author vietnh
import java.util.*;
import java.sql.*;
import java.util.Map.Entry;

/** Class management manipulate database*/
public final class DAO {

    private Connection connection = null;
    private ResultSet resultset = null;
    // Declare types query can use
    private Statement statement = null;
    private PreparedStatement prepareStatement = null;
    private CallableStatement storeProc = null;
    private ResultSet rs = null;
    // Parameters need for connect to database
    private String driver;
    private String url;
    private String urlDirver;
    private String host;
    private String port;
    private String userName;
    private String password;
    private Properties properties = null;
    private String databaseName;
    private String table;
    private Vector<String> fields;
    // Flag
    private int error = 0;
    private String errmsg = null;

    //Constructor init parameters connect database
    public DAO() {
        driver = "net.sourceforge.jtds.jdbc.Driver";
        url = "jdbc:jtds:sqlserver://";
        host = "localhost";
        port = "1433";
        databaseName = "Mantech_Helpdesk";
        userName = "sa";
        password = "123123";
        this.setConnection(connection);
    }

    public void setConnection(Connection con) {
        // Neu chua co driver thi gan loi so 1
        if (driver == null) {
            error = 1;
            return;
        }

        // Neu chua co duong dan toi CSDL thi gan loi so 2
        if (url == null) {
            error = 2;
            return;
        }

        try {
            urlDirver = url + host + ":" + port + "/" + databaseName;
            Class.forName(driver);  // Load driver
            if (properties == null) {
                if ((userName == null) && (password == null)) {
                    con = DriverManager.getConnection(urlDirver);
                } else {
                    con = DriverManager.getConnection(urlDirver, userName, password);
                }
            } else {
                con = DriverManager.getConnection(url, properties);
            }
            connection = con;

        } // Khi gap loi thi nhan thong diep loi tu he thong
        catch (Exception e) {
            errmsg = e.getMessage();
            error = -1;
            System.out.println("Co loi khi tao ket noi");
            return;
        }
        error = 0;
    }

    public Connection getConnection() {
        if (connection == null) {
            error = 3;
        }
        return connection;
    }

    public Statement getStatement() {
        try {
            statement = connection.createStatement();   // Tao doi tuong truy van thong thuong
        } catch (SQLException e) {
            errmsg = "Error : " + e.getSQLState() + "--" + e.getMessage();
            error = -1;
        }
        return statement;
    }

    public PreparedStatement getPrepareStatement(String sql) {
        try {
            prepareStatement = connection.prepareStatement(sql);
        } catch (SQLException e) {
            errmsg = "Error : " + e.getSQLState() + "--" + e.getMessage();
            error = -1;
        }
        return prepareStatement;
    }

    public CallableStatement getStoreProc(String sql) {
        try {
            storeProc = connection.prepareCall(sql);
        } catch (SQLException e) {
            errmsg = "Error : " + e.getSQLState() + "--" + e.getMessage();
            error = -1;
        }
        return storeProc;
    }

    public String getErrmsg() {
        switch (error) {
            case 0:
                errmsg = "No Error !!!";
                break;
            case 1:
                errmsg = "Driver invalid !!!";
                break;
            case 2:
                errmsg = "URL invalid !!!";
                break;
            case 3:
                errmsg = "Connection is not currently established !!!";
                break;
        }
        return errmsg;
    }

    public void destroy() {
        // Thu dong cac ket noi, bang truy van hay cac cau truy van neu no dang mo
        try {
            if (resultset != null) {
                resultset.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (prepareStatement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            errmsg = "Error : " + e.getSQLState() + "--" + e.getMessage();
            error = -1;
        }
    }

    public void insert(Properties pField) {
        String queryString = "INSERT INTO " + table + "(";
        int t = 0;
        // Liet ke ten cac cot theo tu tu gia tri them vao
        for (int i = 0; i < fields.size(); i++) {
            String s = pField.getProperty(fields.get(i));

            if (s != null) {
                if (t != 0) {
                    queryString += ",";
                }
                queryString += fields.get(i);
                t++;
            }
        }
        queryString += ") VALUES (";
        t = 0;
        // Dien cac gia tri can them vao dung vi tri tuong ung
        for (int i = 0; i < fields.size(); i++) {
            String s = pField.getProperty(fields.get(i));

            if (s != null) {
                if (t != 0) {
                    queryString += ",";
                }
                queryString += "'" + pField.getProperty(fields.get(i)) + "'";
                t++;
            }
        }
        queryString += ");";

        try {
            System.out.println(queryString);
            if (statement == null) {
                System.out.println("Statement == NULL");
            }
            statement.executeUpdate(queryString);
        } catch (SQLException sqlException) {
            System.out.println(sqlException);
        }
    }

    public void update(Properties pField) {
        String queryString = "UPDATE " + table + " SET ";
        int t = 0;
        // Liet ke ten cac cot theo tu tu gia tri can cap nhat
        for (int i = 0; i < fields.size(); i++) {
            String s = pField.getProperty(fields.get(i));

            if (s != null) {
                if (t != 0) {
                    queryString += ",";
                }
                queryString += fields.get(i) + "='" + pField.getProperty(fields.get(i)) + "'";
                t++;
            }
        }
        // Cap nhat dua vao truong khoa chinh
        queryString += " WHERE " + fields.get(0) + "='" + pField.getProperty(fields.get(0)) + "'";

        try {
            statement.executeUpdate(queryString);
        } catch (SQLException sqlException) {
            System.out.println(sqlException);
        }
    }

    public void delete(Properties pField) {
        String queryString = "DELETE FROM " + table + " WHERE ";
        int t = 0;
        // Liet ke ten cac cot theo tu tu gia tri can cap nhat
        for (int i = 0; i < fields.size(); i++) {
            String s = pField.getProperty(fields.get(i));
            System.out.println(s);

            if (s != null) {
                if (t != 0) {
                    queryString += " AND ";
                }
                queryString += fields.get(i) + "='" + pField.getProperty(fields.get(i)) + "'";
                t++;
            }
        }

        try {
            System.out.println(queryString);
            statement.executeUpdate(queryString);
        } catch (SQLException sqlException) {
            System.out.println(sqlException);
        }
    }
    /*
     * type Execute is query or update
     */

    public Object executeProc(String procName, Properties params, String typeExecute) {
        int count = 0;
        String callProc = "{call " + procName;

        if (params != null && params.size() > 0) {
            callProc += "(";
            for (int i = 0; i < params.size(); i++) {
                if (count != 0) {
                    callProc += ",";
                }
                callProc += "?";
                count++;
            }
            callProc += ")}";
            count = 0;
            try {
                storeProc = connection.prepareCall(callProc);
                for (Entry entry : params.entrySet()) {
                    storeProc.setString(Integer.parseInt(entry.getKey().toString()), entry.getValue().toString());
                }
                if (typeExecute.equals("query")) {
                    return storeProc.executeQuery();
                } else {
                    return (storeProc.executeUpdate() == 1);
                }
            } catch (SQLException ex) {
                System.out.println(ex.getMessage());
                return false;
            }
        } else {
            callProc += "()}";
            try {
                storeProc = connection.prepareCall(callProc);
                if (typeExecute.equals("query")) {
                    return storeProc.executeQuery();
                } else {
                    return (storeProc.executeUpdate() == 1);
                }
            } catch (SQLException ex) {
                System.out.println(ex.getMessage());
                return false;
            }
        }
    }
    /*
     *Function Getter and Setter
     */

    public int getError() {
        return error;
    }

    public String getDatabaseName() {
        return databaseName;
    }

    public void setDatabaseName(String databaseName) {
        this.databaseName = databaseName;
    }

    public String getDriver() {
        return driver;
    }

    public void setDriver(String driver) {
        this.driver = driver;
    }

    public Vector<String> getFields() {
        return fields;
    }

    public void setFields(Vector<String> fields) {
        this.fields = fields;
    }

    public String getHost() {
        return host;
    }

    public void setHost(String host) {
        this.host = host;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getPort() {
        return port;
    }

    public void setPort(String port) {
        this.port = port;
    }

    public PreparedStatement getPrepareStatement() {
        return prepareStatement;
    }

    public void setPrepareStatement(PreparedStatement prepareStatement) {
        this.prepareStatement = prepareStatement;
    }

    public Properties getProperties() {
        return properties;
    }

    public void setProperties(Properties properties) {
        this.properties = properties;
    }

    public ResultSet getResultset() {
        return resultset;
    }

    public void setResultset(ResultSet resultset) {
        this.resultset = resultset;
    }

    public CallableStatement getStoreProc() {
        return storeProc;
    }

    public void setStoreProc(CallableStatement storeProc) {
        this.storeProc = storeProc;
    }

    public String getTable() {
        return table;
    }

    public void setTable(String table) {
        this.table = table;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public String getUrlDirver() {
        return urlDirver;
    }

    public void setUrlDirver(String urlDirver) {
        this.urlDirver = urlDirver;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }
}